package me.whf.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class SQLHelper {
	
	public static Connection conn;
	static {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://127.0.0.1:3306/bankmgrsystem";
		String user = "root";
		String password = "root";
		
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void SQLDML(ArrayList<String> sqls) {
		Statement statement = null;
		try {
			statement = SQLHelper.conn.createStatement();
			for (String sql: sqls) {
				statement.addBatch(sql);
			}
			statement.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				statement = null;
			}
		}
	}

	public List<HashMap<String, Object>> getQueryList(String sql) {
		ResultSet rs = getResultSet(sql);
		List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
		ResultSetMetaData md = null;
		int columnCount = 0;
		
		try {
			md = rs.getMetaData();
			columnCount = md.getColumnCount();
	
			while (rs.next()) {
				HashMap<String, Object> rowData = new HashMap<String, Object>();
				
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
					rs = null;
				}
			}
		}		
		return list;
	}

	private ResultSet getResultSet(String sql) {
		ResultSet rs = null;
		Statement statement = null;
		try {
			statement = conn.createStatement();
			rs = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
}
